﻿
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using PowerCreator.LMS.Common;
using PowerCreator.LMS.Common.Utils;
using PowerCreator.LMS.Common.Script;
using System.Text;

using PowerCreator.LMS.DL.Project.Entity;
using PowerCreator.LMS.Common.Script;
using PowerCreator.LMS.Common.Utils;
using PowerCreator.LMS.Web.DL.Project;
using PowerCreator.LMS.Web.Catalog;
using PowerCreator.LMS.Web.User;
using PowerCreator.LMS.Web.Course;
using Telerik.WebControls;



public partial class sysadmin_DL_Project_UserExportByExcel : PowerCreator.LMS.Web.Base.BasePage
{

    TOLearner obj = new TOLearner();

    //PowerCreator.LMS.Web.User.Department objDepartment = new PowerCreator.LMS.Web.User.Department();
    //PowerCreator.LMS.User.Entity.VOTDepartment objDepartmentInfo = new VOTDepartment();

    //PowerCreator.LMS.Web.User.User objUser = new PowerCreator.LMS.Web.User.User();
    //LogicServer.NetMeetingClass objNetMeetingClass = new LogicServer.NetMeetingClass();

    private string ExcelName_New = string.Empty;//实际文件名称
    private string strFileName = string.Empty;//文件格式
    private string error = string.Empty;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            InitPage();
            BinderRegion();
            BinderSchool(int.Parse(ddl_Region.SelectedValue));
            
            

        }
    }
    
    protected override void InitPage()
    {
        base.InitPage();
    }

    //绑定区域
    private void BinderRegion()
    {
        TORegion obj = new TORegion();
        string search = "";
        DataTable dt = obj.Query(search);

        ddl_Region.DataSource = dt;
        ddl_Region.DataTextField = "Region";
        ddl_Region.DataValueField = "ID";
        ddl_Region.DataBind();

    }

    //绑定学校
    private void BinderSchool(int RegionID)
    {
        TOSchoolGroup obj = new TOSchoolGroup();
        string search = "1=1";
        if (RegionID.ToString() != "0")
        {
            VOTORegion voRegion = new VOTORegion();
            TORegion objRegion = new TORegion();
            voRegion = objRegion.Load(RegionID);
            search += " and SchoolGroupRegion='" + voRegion.Region + "'";
        }

        DataTable dt = obj.Query(search);

        ddl_School.DataSource = dt;
        ddl_School.DataTextField = "SchoolGroupName";
        ddl_School.DataValueField = "SchoolGroup_Id";
        ddl_School.DataBind();


    }


    protected void btnSave_Click(object sender, EventArgs e)
    {

        
        string GeneralPath = Server.MapPath(WebUtils.WebRoot + "//General.config");
        lab_msg.Visible = true;
        StringBuilder str_msg = new StringBuilder();
       
        if (!UploadFile())
        {
            str_msg.Append(GetLanageValueByKey("UserExportByExcel_Msg1", PowerCreator.LMS.Language.LanagetItemType.Page) + error);
            lab_msg.Text = GetLanageValueByKey("UserExportByExcel_Msg2", PowerCreator.LMS.Language.LanagetItemType.Page) + str_msg.ToString();
            return;
        }

        string msg = GetExcelDate(ExcelName_New, GeneralPath);

        lab_msg.Text = GetLanageValueByKey("UserExportByExcel_Msg2", PowerCreator.LMS.Language.LanagetItemType.Page) + str_msg.ToString() + msg;
     
    }

    //上传软件
    bool UploadFile()
    {
        if (File1.PostedFile.FileName.Length < 1)
        {
            Script.Alert(Page, "msg", GetLanageValueByKey("UserExportByExcel_Msg3", PowerCreator.LMS.Language.LanagetItemType.Page));
            return false;
        }
        string strName = this.File1.PostedFile.FileName.ToString();
        int nlength = strName.Length - strName.LastIndexOf("\\") - 1;
        string ImgName = strName.Substring(strName.LastIndexOf("\\") + 1, nlength);
        //获取软件格式
        strFileName = strName.Substring(strName.LastIndexOf(".")).Replace(".", "");
        if (strFileName.ToLower() != "xls")
        {
            error = GetLanageValueByKey("UserExportByExcel_Msg4", PowerCreator.LMS.Language.LanagetItemType.Page);
            return false;
        }

        ExcelName_New = Guid.NewGuid() + "." + strFileName.ToLower();
        string sPath = AppDomain.CurrentDomain.BaseDirectory.ToString() + "QuestionDocument\\" + ExcelName_New;
        //string sPath = Server.MapPath(Utils.WebRoot + "\\QuestionDocument\\" + ExcelName_New + ".word"); +"QuestionDocument\\" + ExcelName_New;
        try
        {
            this.File1.PostedFile.SaveAs(sPath);
            return true;
        }
        catch(Exception ex)
        {
            error = ex.Message;
            return false;
        }

    }
    protected void btn_DownLoad_Click(object sender, EventArgs e)
    {
        Script.RedirectURL(WebUtils.WebRoot + "/QuestionDocument/AddUser_Demo.xls");
    }

    //SQL方式读取
    public DataTable GetExcelDateBySQL(string newFileName, ref string eror, string GeneralPath)
    {

        DataTable dt = new DataTable();
        string filePath = Server.MapPath(WebUtils.WebRoot + "\\QuestionDocument\\" + newFileName);
        //private string GeneralFilePath=
        string conn = PowerCreator.LMS.Data.XML.GeneralSetting.GetSetting(GeneralPath)["GetExcelConnectionstring"];

        //SqlConnection connection = new SqlConnection("server=CHINESE-296470C\\POWERCREATOR;uid=sa;pwd=123456;database=master;");
        SqlConnection connection = new SqlConnection(conn);
        //string cmd = "select * from  OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=F:\\lms\\1defff82-9555-4e06-9151-ef08f9881ba1.xls',sheet1$)";
        string cmd = string.Format(System.Configuration.ConfigurationManager.AppSettings["SelectExcelCmd"].ToString(), filePath);
        SqlDataAdapter da = new SqlDataAdapter(cmd, connection);
        DataSet ds = new DataSet();
        try
        {
            da.Fill(ds);
        }
        catch (Exception ex)
        {
            eror = ex.Message;
            return null;
        }
        dt = ds.Tables[0];
        return dt;
    }


    public string GetExcelDate(string newFileName, string GeneralPath)
    {
        StringBuilder msg = new StringBuilder();
        string str_eror = string.Empty;
        DataTable dt_Excel = new DataTable();


        string AddUserType = System.Configuration.ConfigurationManager.AppSettings["AddUserType"].ToString();
        if (AddUserType == "Sqlserver")
        {
            dt_Excel = GetExcelDateBySQL(newFileName, ref str_eror, GeneralPath);
            msg.Append(str_eror);
        }
        else if (AddUserType == "Oledb")
        {
            string filePath = Server.MapPath(WebUtils.WebRoot + "\\QuestionDocument\\" + newFileName);
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;";
            string cmd = "select * from [Sheet1$]";
            OleDbDataAdapter da = new OleDbDataAdapter(cmd, strConn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            dt_Excel = ds.Tables[0];
            msg.Append(str_eror);
        }
        //else if (AddUserType == "Dcom")
        //{
        //    return GetExcelDateByDCOM(newFileName);

        //}

        //string filePath = Server.MapPath(Utils.WebRoot + "\\QuestionDocument\\" + newFileName);
        //dt_Excel = objexcel.ExcelSheetName(filePath, ref str_eror);
        //msg.Append(str_eror);


        if (dt_Excel == null)
        {
            msg.Append(GetLanageValueByKey("UserExportByExcel_Msg5", PowerCreator.LMS.Language.LanagetItemType.Page));
            return msg.ToString();
        }
        if (dt_Excel.Rows.Count == 0)
        {

            msg.Append(GetLanageValueByKey("UserExportByExcel_Msg5", PowerCreator.LMS.Language.LanagetItemType.Page));
            return msg.ToString();
        }
        int count = 0;
        try
        {


            for (int i = 0; i < dt_Excel.Rows.Count; i++)
            {
                count += 1;

                VOTOLearner vo = new VOTOLearner();


                vo.UserName = dt_Excel.Rows[i]["帐户"].ToString();
                if (vo.UserName == "")
                {
                    msg.Append("第" + count.ToString() + "行记录：帐户信息有误。" + "</br>");
                    continue;
                }

                if (obj.Query("UserName='" + vo.UserName + "'").Rows.Count > 0)
                {
                    msg.Append("第" + count.ToString() + "行记录：帐户已存在。" + "</br>");
                    continue;
                }
                //string spwd = dt_Excel.Rows[i]["密码"].ToString();
                //objUserInfo.UserPassword = MD5Helper.GetMD5(dt_Excel.Rows[i]["密码"].ToString());
                vo.UserPwd = dt_Excel.Rows[i]["密码"].ToString();
                if (vo.UserPwd == "")
                {
                    msg.Append("第" + count.ToString() + "行记录：密码信息有误。" + "</br>");
                    continue;
                }
                else
                    vo.UserPwd = PowerCreator.LMS.Common.Utils.MD5Helper.GetMD5(vo.UserPwd);

                vo.Name = dt_Excel.Rows[i]["姓名"].ToString();
                try
                {
                    int teacherid = int.Parse(dt_Excel.Rows[i]["教师流水号"].ToString());
                    vo.TeacherId=teacherid;
                }
                catch (Exception ex)
                {
                    msg.Append("第" + count.ToString() + "行记录：教师流水号信息有误。" + "</br>");
                    continue;
                }
                if (dt_Excel.Rows[i]["性别"].ToString().Trim() != "")
                {
                    string sex = dt_Excel.Rows[i]["性别"].ToString().Trim();
                    try
                    {
                        if (dt_Excel.Rows[i]["性别"].ToString().Trim() == "女")
                            vo.Sex = true;
                        else if (dt_Excel.Rows[i]["性别"].ToString().Trim() == "男")
                            vo.Sex = false;
                        else
                            vo.Sex = false;
                    }
                    catch
                    {
                        msg.Append("第" + count.ToString() + "行记录：性别信息错误。" + "</br>");
                        continue;
                    }
                }
                else
                    vo.Sex = false;

                if (dt_Excel.Rows[i]["出生日期"].ToString().Trim() != "")
                {
                    string birthday = dt_Excel.Rows[i]["出生日期"].ToString();

                    try
                    {
                        vo.Birthday = Convert.ToDateTime(birthday);
                    }
                    catch
                    {
                        msg.Append("第" + count.ToString() + "行记录：生日信息错误。" + "</br>");
                        continue;
                    }
                }
                if (dt_Excel.Rows[i]["电子邮箱"].ToString().Trim() != "")
                {
                    vo.Email = dt_Excel.Rows[i]["电子邮箱"].ToString();
                    //if (objStudentInfo.StudentEmail == "")
                    //{
                    //    msg.Append("第" + count.ToString() + "行记录：电子邮箱信息有误。" + "</br>");
                    //    continue;
                    //}
                }

                vo.Mobile = dt_Excel.Rows[i]["电话"].ToString();

                if (chkbx_ok.Checked)
                {
                    vo.Region = int.Parse(ddl_Region.SelectedValue);
                    vo.School = int.Parse(ddl_School.SelectedValue);
                }
                else
                {
                    TORegion objRegion = new TORegion();
                    string regionName = dt_Excel.Rows[i]["所属区域"].ToString().Trim();
                    if (regionName != "")
                    {

                        DataTable dt_region = objRegion.Query("Region='" + regionName + "'");
                        if (dt_region.Rows.Count > 0)
                        {
                            vo.Region = int.Parse(dt_region.Rows[0]["ID"].ToString());
                        }
                        else
                        {
                            msg.Append("第" + count.ToString() + "行记录：所属区域信息数据在系统中不存在。" + "</br>");
                            continue;
                        }
                    }




                    TOSchoolGroup objSchool = new TOSchoolGroup();
                    string schoolname = dt_Excel.Rows[i]["所在学校"].ToString().Trim();
                    if (schoolname != "")
                    {
                        DataTable dt_School = objSchool.Query("SchoolGroupName='" + schoolname + "'");
                        if (dt_School.Rows.Count > 0)
                        {
                            if (dt_School.Rows[0]["SchoolGroupRegion"].ToString() == regionName)
                                vo.School = int.Parse(dt_School.Rows[0]["SchoolGroup_Id"].ToString());
                            else
                            {
                                msg.Append("第" + count.ToString() + "行记录：所在学校与所属区域信息不对应。" + "</br>");
                                continue;
                            }

                        }
                        else
                        {
                            msg.Append("第" + count.ToString() + "行记录：所在学校信息数据在系统中不存在。" + "</br>");
                            continue;
                        }
                    }
                }


                TOSchoolStage objStage = new TOSchoolStage();
                string stageName = dt_Excel.Rows[i]["学段"].ToString().Trim();
                if (stageName != "")
                {
                    DataTable dt_stage = objStage.Query("SchoolStage='" + stageName + "'");
                    if (dt_stage.Rows.Count > 0)
                    {
                        vo.XueDuan = int.Parse(dt_stage.Rows[0]["ID"].ToString());
                    }
                    else
                    {
                        msg.Append("第" + count.ToString() + "行记录：学段信息数据在系统中不存在。" + "</br>");
                        continue;
                    }
                }

                Catalog objcat = new Catalog();
                string catalogName = dt_Excel.Rows[i]["学科"].ToString().Trim();
                if (catalogName != "")
                {
                    DataTable dt_catalog = objcat.QueryCatalog("CatalogName='" + catalogName + "'");
                    if (dt_catalog.Rows.Count > 0)
                    {
                        vo.XueKe = int.Parse(dt_catalog.Rows[0]["CatalogId"].ToString());
                    }
                    else
                    {
                        msg.Append("第" + count.ToString() + "行记录：学科信息数据在系统中不存在。" + "</br>");
                        continue;
                    }
                }

                TOBaseClass objbase=new TOBaseClass();
                string xuenian = dt_Excel.Rows[i]["学年"].ToString().Trim();
                if (xuenian != "")
                {
                    int xuenianval=objbase.getXueNianID(xuenian);
                    if (xuenianval == 0)
                    {
                        msg.Append("第" + count.ToString() + "行记录：学年信息数据不正确。" + "</br>");
                        continue;
                    }
                    else
                        vo.XueNian = xuenianval;
                }

                Duty objduty = new Duty();
                string dutyName = dt_Excel.Rows[i]["职称"].ToString().Trim();
                if (dutyName!= "")
                {
                    DataTable dt_duty = objduty.Query("TeadutyName='" + dutyName + "'");
                    if (dt_duty.Rows.Count > 0)
                    {
                        vo.ZhiCheng = int.Parse(dt_duty.Rows[0]["TeadutyId"].ToString());
                    }
                    else
                    {
                        msg.Append("第" + count.ToString() + "行记录：职称信息数据在系统中不存在。" + "</br>");
                        continue;
                    }
                }

                Job objjob = new Job();
                string jobName = dt_Excel.Rows[i]["职务"].ToString().Trim();
                if (jobName != "")
                {
                    DataTable dt_job = objjob.Query("TeajobName='" + jobName + "'");
                    if (dt_job.Rows.Count > 0)
                    {
                        vo.XueKe = int.Parse(dt_job.Rows[0]["TeajobId"].ToString());
                    }
                    else
                    {
                        msg.Append("第" + count.ToString() + "行记录：职务信息数据在系统中不存在。" + "</br>");
                        continue;
                    }
                }
                
                


                string xueli = dt_Excel.Rows[i]["当前学历"].ToString().Trim();
                if (xueli != "")
                {
                    int xueliVal = objbase.getXueLiID(xueli);
                    if (xueliVal == 0)
                    {
                        msg.Append("第" + count.ToString() + "行记录：当前学历信息数据不正确。" + "</br>");
                        continue;
                    }
                    else
                        vo.Education = xueliVal;
                }
                string sepcial = dt_Excel.Rows[i]["特殊分类"].ToString().Trim();
                if (sepcial != "")
                {
                    int sepcialval = objbase.getSepcialID(sepcial);
                    if (sepcialval == 0)
                    {
                        msg.Append("第" + count.ToString() + "行记录：特殊分类信息数据不正确。" + "</br>");
                        continue;
                    }
                    else
                        vo.XueNian = sepcialval;
                }

                vo.LoginNum = 0;
                vo.UserGroup = 0;
              
               

                 if (!obj.Add(vo))
                 {

                     msg.Append("第" + count.ToString() + "行记录：导入失败。" + "</br>");

                 }
                 else
                 {
                     //objNetMeetingClass.WebAddUser(UserID, objUserInfo.UserName, objUserInfo.UserPassword, (ulong)objUserInfo.UserRule, objUserInfo.UserImageIndex, objStudentInfo.StudentDescription, objStudentInfo.StudentSex, objStudentInfo.StudentTelephone, objUserInfo.UserType);
                     msg.Append("第" + count.ToString() + "行记录：导入成功。" + "</br>");

                 }
                
          


            }


        }
        catch (Exception ex)
        {
            msg.Append("第" + count.ToString() + "行记录：导入失败。" + "(" + ex.Message + ")" + "</br>");
        }
        return msg.ToString();
    }


    protected void chkbx_ok_CheckedChanged(object sender, EventArgs e)
    {
        if (chkbx_ok.Checked)
        {
            ddl_Region.Visible = true;
            ddl_School.Visible = true;
        }
        else
        {
            ddl_Region.Visible = false;
            ddl_School.Visible = false;
        }
    }

    protected void ddl_Region_SelectedIndexChanged(object sender, EventArgs e)
    {
        BinderSchool(int.Parse(ddl_Region.SelectedValue));
    }
}
